package com.diamond.facade;

import java.io.UnsupportedEncodingException;
import java.net.URLDecoder;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.lang.StringUtils;

import com.diamond.action.Page;
import com.diamond.bo.DiamondBO;
import com.diamond.bo.ReturnBO;
import com.diamond.bo.SDiamondBO;
import com.diamond.entity.Diamond;
import com.diamond.entity.Formula;
import com.diamond.service.IDiamondService;
import com.diamond.util.DiamondUtil;
import com.diamond.util.QueryDiamondAtt;

public class DiamondFacade extends CommonFacade<DiamondBO> {
	private FormulaFacade formulaFacade;
	
	private IDiamondService diamondService;

	public void setDiamondService(IDiamondService diamondService) {
		this.diamondService = diamondService;
	}

	/**
	 * 查询零售价
	 * @param request
	 * @param session
	 * @param url
	 * @param sDiamondBO
	 * @return
	 */
	public List getSale(HttpServletRequest request,Map session,String url,SDiamondBO sDiamondBO){
		try {
			String[] sqlAndUrl = newSQL(session,sDiamondBO,url,1);
			String SQL=sqlAndUrl[0];
			SQL+=" order by supPrice";
			List<DiamondBO> list = Page.stageUtil(DiamondBO.class,request,sqlAndUrl[1],this,SQL,30);
			if(list==null||list.size()==0)return null;
			for(DiamondBO diamondBO:list){
				if(diamondBO.getSpePrice()!=0){
					diamondBO.setBuyPrice(diamondBO.getSpePrice());
				}
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * 查询特价
	 * @param request
	 * @param session
	 * @param url
	 * @param sDiamondBO
	 * @return
	 */
	public List getSpe(HttpServletRequest request,Map session,String url,SDiamondBO sDiamondBO){
		try {
			String[] sqlAndUrl = newSQL(session,sDiamondBO,url,2);
			String SQL=sqlAndUrl[0];
			SQL+=" order by spePrice";
			List<DiamondBO> list = Page.stageUtil(DiamondBO.class,request,sqlAndUrl[1],this,SQL,20);
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * 查询经销商成本价
	 * @param request
	 * @param session
	 * @param url
	 * @param sDiamondBO
	 * @return
	 */
	public List getSaleCost(HttpServletRequest request,Map session,String url,SDiamondBO sDiamondBO){
		try {
			String[] sqlAndUrl = newSQL(session,sDiamondBO,url,3);
			String SQL=sqlAndUrl[0];
			SQL+=" order by sup_price";
			List<DiamondBO> list = Page.stageUtil(DiamondBO.class,request,sqlAndUrl[1],this,SQL,20);
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	/**
	 * 查询代理商成本价
	 * @param request
	 * @param session
	 * @param url
	 * @param sDiamondBO
	 * @return
	 */
	public List getAngentCost(HttpServletRequest request,Map session,String url,SDiamondBO sDiamondBO){
		try {
			String[] sqlAndUrl = newSQL(session,sDiamondBO,url,4);
			String SQL=sqlAndUrl[0];
			SQL+=" order by sup_price";
			List<DiamondBO> list = Page.stageUtil(DiamondBO.class,request,sqlAndUrl[1],this,SQL,20);
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public ReturnBO<DiamondBO> getBackCost(HttpServletRequest request,String url,SDiamondBO sDiamondBo){
		ReturnBO<DiamondBO> returnBo = new ReturnBO<DiamondBO>();
		List<DiamondBO> diamondBolist = new ArrayList<DiamondBO>();
		try {
			List<?> supFormulas=formulaFacade.findSupplyFormula();
			if(supFormulas==null||supFormulas.size()==0)return null;
			String[] sqlAndUrl = initSearchSQL(sDiamondBo,url,1);
			String SQL=sqlAndUrl[0];
//			System.out.println("SSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSS :                " + SQL);
			ReturnBO<Diamond> rBo = Page.backUtil(request, url, this, SQL, sDiamondBo.getStart(), sDiamondBo.getPageSize());
			List<Diamond> list = rBo.getReturnBolist();
			if(list!=null&&list.size()>0){
				for(int i=0;i<list.size();i++){
					Diamond bo = list.get(i);
					DiamondBO diamondBo = new DiamondBO();
//					PropertyUtils.copyProperties(bo, diamondBo);
					for(int k=0;k<supFormulas.size();k++){
						Formula supFormula=(Formula) supFormulas.get(k);
						if(DiamondUtil.suitFormula(supFormula, bo)){
							if (null != bo.getSupPrice() && !"".equals(bo.getSupPrice()) && !"0".equals(bo.getSupPrice())) {
								Double price=bo.getSupPrice()*supFormula.getRate();
								bo.setMidPrice(price.doubleValue());
							}
							PropertyUtils.copyProperties(diamondBo, bo);
							diamondBolist.add(diamondBo);
							break;
						}
					}
				}
			}
			returnBo.setPageSize(rBo.getPageSize());
			returnBo.setStart(rBo.getStart());
			returnBo.setTotalSize(rBo.getTotalSize());
			returnBo.setReturnBolist(diamondBolist);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return returnBo;
	}
	/**
	 * 组装钻石查询语句组装SQL
	 * @param sDiamondBO
	 * @param url
	 * @param type
	 * @return
	 */
	private String[] initSearchSQL(SDiamondBO sDiamondBO,String url,Integer type) {
		String SQL="";
		if(type==1){
			SQL="from Diamond where 1=1 ";
		}else{
			SQL="from Diamond where 1=2 ";
		}
		if(sDiamondBO!=null){
			if(!StringUtils.isEmpty(sDiamondBO.getMinWeight())){
				url+="&sDiamondBo.minWeight="+sDiamondBO.getMinWeight();
				SQL+="and weight>="+sDiamondBO.getMinWeight()+" ";
			}
			if(!StringUtils.isEmpty(sDiamondBO.getMaxWeight())){
				url+="&sDiamondBo.maxWeight="+sDiamondBO.getMaxWeight();
				SQL+="and weight<="+sDiamondBO.getMaxWeight()+" ";
			}	
			if(!StringUtils.isEmpty(sDiamondBO.getMinPrice())){
				SQL+="and supPrice>="+sDiamondBO.getMinPrice()+" ";
			}
			if(!StringUtils.isEmpty(sDiamondBO.getMaxPrice())){
				SQL+="and supPrice<="+sDiamondBO.getMaxPrice()+" ";
			}
			if(sDiamondBO.getColour()!=null&&sDiamondBO.getColour().length>0 && !"".equals((sDiamondBO.getColour())[0])){
				SQL+="and color in(";
				String[] strs=sDiamondBO.getColour();
				for(int j=0;j<strs.length;j++){
					if(j<strs.length-1)SQL+="'"+strs[j]+"',";
					else SQL+="'"+strs[j]+"'";
					url+="&sDiamondBo.colour="+strs[j];
				}
				SQL+=") ";
			}
			if(sDiamondBO.getPurity()!=null&&sDiamondBO.getPurity().length>0 && !"".equals((sDiamondBO.getPurity())[0])){
				SQL+="and clarity in(";
				String[] strs=sDiamondBO.getPurity();
				for(int j=0;j<strs.length;j++){
					if(j<strs.length-1)SQL+="'"+strs[j]+"',";
					else SQL+="'"+strs[j]+"'";
					url+="&sDiamondBo.purity="+strs[j];
				}
				SQL+=") ";
			}
			if(sDiamondBO.getCutter()!=null&&sDiamondBO.getCutter().length>0 && !"".equals((sDiamondBO.getCutter())[0])){
				SQL+="and finalCut in("+QueryDiamondAtt.getDiamondAttToCut(sDiamondBO)+") ";
//				String[] strs=sDiamondBO.getCutter();
//				for(int j=0;j<strs.length;j++){
//					if(j<strs.length-1)SQL+="'"+strs[j]+"',";
//					else SQL+="'"+strs[j]+"'";
//					url+="&sDiamondBo.cutter="+strs[j];
//				}
//				SQL+=") ";
			}
			if(sDiamondBO.getSymmetry()!=null&&sDiamondBO.getSymmetry().length>0 && !"".equals((sDiamondBO.getSymmetry())[0])){
				SQL+="and symmetry in(" + QueryDiamondAtt.getDiamondAttToSymmetry(sDiamondBO)+") ";
//				String[] strs=sDiamondBO.getSymmetry();
//				for(int j=0;j<strs.length;j++){
//					if(j<strs.length-1)SQL+="'"+strs[j]+"',";
//					else SQL+="'"+strs[j]+"'";
//					url+="&sDiamondBo.symmetry="+strs[j];
//				}
//				SQL+=") ";
			}
			if(sDiamondBO.getBuffing()!=null&&sDiamondBO.getBuffing().length>0 && !"".equals((sDiamondBO.getBuffing())[0])){
				SQL+="and polish in(" + QueryDiamondAtt.getDiamondAttToPolish(sDiamondBO)+") ";
//				String[] strs=sDiamondBO.getBuffing();
//				for(int j=0;j<strs.length;j++){
//					if(j<strs.length-1)SQL+="'"+strs[j]+"',";
//					else SQL+="'"+strs[j]+"'";
//					url+="&sDiamondBo.buffing="+strs[j];
//				}
//				SQL+=") ";
			}
			if(sDiamondBO.getFluorescence()!=null&&sDiamondBO.getFluorescence().length>0 && !"".equals((sDiamondBO.getFluorescence())[0])){
				SQL+="and fluor in(" + QueryDiamondAtt.getDiamondAttToFluor(sDiamondBO)+") ";
//				String[] strs=sDiamondBO.getFluorescence();
//				for(int j=0;j<strs.length;j++){
//					if(j<strs.length-1)SQL+="'"+strs[j]+"',";
//					else SQL+="'"+strs[j]+"'";
//					url+="&sDiamondBo.fluorescence="+strs[j];
//				}
//				SQL+=") ";
			}
			if(sDiamondBO.getCerificate()!=null&&sDiamondBO.getCerificate().length>0 && !"".equals((sDiamondBO.getCerificate())[0])){
				
				SQL+="and lab in(";
				String[] strs=sDiamondBO.getCerificate();
				for(int j=0;j<strs.length;j++){
					try {
						strs[j]=URLDecoder.decode(strs[j],"UTF-8");
					} catch (UnsupportedEncodingException e) {
						e.printStackTrace();
					}
					if(j<strs.length-1)SQL+="'"+strs[j]+"',";
					else SQL+="'"+strs[j]+"'";
					url+="&sDiamondBo.cerificate="+strs[j];
				}
				sDiamondBO.setCerificate(strs);
				SQL+=") ";
			}
		}
		SQL+=" order by sup_price";
		return new String[]{SQL,url};
	}

	/**
	 * 根据公式组装SQL
	 * @param SQL
	 * @param speFormulas
	 * @param type 1:普通查询,2:特价查询,3:供应价查询
	 * @return
	 */
	private String initSQLBySpeFormula(String SQL, List speFormulas) {
		for(int i=0;i<speFormulas.size();i++){
			SQL+="or diamondId in (select diamondId from Diamond  where 1=1 ";
			Formula formula = (Formula) speFormulas.get(i);
			String str=formula.getWeight();
			if(!StringUtils.isEmpty(str)){
				String[] strs=str.split("-");
				if(!StringUtils.isEmpty(strs[0]))SQL+="and weight>="+Double.parseDouble(strs[0])+" ";
				if(!StringUtils.isEmpty(strs[1]))SQL+="and weight<="+Double.parseDouble(strs[1])+" ";
			}
			if(!StringUtils.isEmpty(formula.getColour())){
				SQL+="and color in('"+formula.getColour().replace("|","','")+"') ";
			}
			if(!StringUtils.isEmpty(formula.getPurity())){
				SQL+="and clarity in('"+formula.getPurity().replace("|","','")+"') ";
			}
			if(!StringUtils.isEmpty(formula.getCutter())){
				SQL+="and finalCut in('"+formula.getCutter().replace("|","','")+"') ";
			}
			if(!StringUtils.isEmpty(formula.getSymmetry())){
				SQL+="and symmetry in('"+formula.getSymmetry().replace("|","','")+"') ";
			}
			if(!StringUtils.isEmpty(formula.getBuffing())){
				SQL+="and polish in('"+formula.getBuffing().replace("|","','")+"') ";
			}
			if(!StringUtils.isEmpty(formula.getFluorescence())){
				SQL+="and fluor in('"+formula.getFluorescence().replace("|","','")+"') ";
			}
			if(!StringUtils.isEmpty(formula.getCerificate())){
				SQL+="and lab in('"+formula.getCerificate().replace("|","','")+"') ";
			}
			SQL+=") ";
		}
		return SQL;
	}

	public void setFormulaFacade(FormulaFacade formulaFacade) {
		this.formulaFacade = formulaFacade;
	}

	public void saveOrUpdate(List<DiamondBO> list, String uploadType) throws Exception {
		diamondService.saveOrUpdate(list, uploadType);
	}

	public DiamondBO findDiaById(long diaID,Long userId) {
		try {
			Diamond dia=(Diamond) diamondService.findById(diaID);
			if(dia!=null){
				DiamondBO dest = new DiamondBO();
				PropertyUtils.copyProperties(dest, dia);
				if("GIA".equalsIgnoreCase(dest.getLab())){
					String url="http://www2.gia.edu/reportcheck/index.cfm?fuseaction=home.showReportVerification&reportno=";
					url+=dest.getReportNo();
					url+="&weight=";
					url+=dest.getWeight();
					dest.setCertificateLink(url);
				}
				List formulas=formulaFacade.findFormulas(userId);
				List speFormulas=formulaFacade.findSpeFormula(userId);
				List supFormulas=formulaFacade.findSupplyFormula();
				Double buyPrice = DiamondUtil.getBuyOrSpePrice(dia, supFormulas, speFormulas);
				if(buyPrice==null)buyPrice=DiamondUtil.getBuyOrSpePrice(dia, supFormulas, formulas);
				dest.setBuyPrice(buyPrice);
				return dest;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	public DiamondBO findSpeDiaById(long diaID,Long userId) {
		try {
			Diamond dia=(Diamond) diamondService.findById(diaID);
			if(dia!=null){
				DiamondBO dest = new DiamondBO();
				PropertyUtils.copyProperties(dest, dia);
				if("GIA".equalsIgnoreCase(dest.getLab())){
					String url="http://www2.gia.edu/reportcheck/index.cfm?fuseaction=home.showReportVerification&reportno=";
					url+=dest.getReportNo();
					url+="&weight=";
					url+=dest.getWeight();
					dest.setCertificateLink(url);
				}
				List speFormulas=formulaFacade.findSpeFormula(userId);
				List supFormulas=formulaFacade.findSupplyFormula();
				Double spePrice = DiamondUtil.getBuyOrSpePrice(dia, supFormulas, speFormulas);
				dest.setBuyPrice(spePrice);
				dest.setSpePrice(spePrice);
				return dest;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	public DiamondBO findMidDiaById(long diaID,Long userId) {
		try {
			Diamond dia=(Diamond) diamondService.findById(diaID);
			if(dia!=null){
				DiamondBO dest = new DiamondBO();
				PropertyUtils.copyProperties(dest, dia);
				if("GIA".equalsIgnoreCase(dest.getLab())){
					String url="http://www2.gia.edu/reportcheck/index.cfm?fuseaction=home.showReportVerification&reportno=";
					url+=dest.getReportNo();
					url+="&weight=";
					url+=dest.getWeight();
					dest.setCertificateLink(url);
				}
				List supFormulas=formulaFacade.findSupplyFormula();
				Double midPrice = DiamondUtil.getMidPrice(dia, supFormulas);
				dest.setMidPrice(midPrice);
				return dest;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * 根据查询语句组装SQL
	 * @param sDiamondBO
	 * @param url
	 * @param type 1:零售价,2:特价,3:经销商成本价,4:代理商成本价
	 * @return
	 */
	private String[] newSQL(Map session,SDiamondBO sDiamondBO,String url,Integer type) {
		StringBuffer SQL=new StringBuffer("select *,");
		StringBuffer URL=new StringBuffer(url);
		Long userid = (Long) session.get("USER_ID");
		Object angendId = session.get("ANGENT_ID");
		
		List supFormulas =formulaFacade.findSupplyFormula();
		String suPriceSQL="sup_price*"+builPriceSQL(supFormulas);
		SQL.append(suPriceSQL+" as supPrice ");
		
		String angentPriceSQL=suPriceSQL;
		if("B".equals(session.get("USER_TYPE"))){
			List angentFormulas=formulaFacade.findAngentFormula((Long) userid);
			angentPriceSQL=suPriceSQL+"*"+builPriceSQL(angentFormulas);
		}else if(angendId!=null){
			List angentFormulas=formulaFacade.findAngentFormula((Long) angendId);
			angentPriceSQL=suPriceSQL+"*"+builPriceSQL(angentFormulas);
		}
		
		SQL.append(","+angentPriceSQL+" as midPrice");
		
		String spePriceSQL=angentPriceSQL;
		String salePriceSQL=angentPriceSQL;
		if(1==type||2==type){
			List speFormulas=formulaFacade.findSpeFormula(userid);
			List saleFormulas =formulaFacade.findFormulas(userid);
			spePriceSQL=angentPriceSQL+"*"+builPriceSQL(speFormulas);
			salePriceSQL=angentPriceSQL+"*"+builPriceSQL(saleFormulas);
			SQL.append(","+spePriceSQL+" as spePrice ");
			SQL.append(","+salePriceSQL+" as buyPrice ");
		}
		SQL.append(" from diamond where 1=1 ");
		if(sDiamondBO!=null){
			if(!StringUtils.isEmpty(sDiamondBO.getMinWeight())){
				URL.append("&sDiamondBo.minWeight="+sDiamondBO.getMinWeight());
				SQL.append("and weight>="+sDiamondBO.getMinWeight()+" ");
			}
			if(!StringUtils.isEmpty(sDiamondBO.getMaxWeight())){
				URL.append("&sDiamondBo.maxWeight="+sDiamondBO.getMaxWeight());
				SQL.append("and weight<="+sDiamondBO.getMaxWeight()+" ");
			}	
			if(sDiamondBO.getColour()!=null&&sDiamondBO.getColour().length>0 && !"".equals((sDiamondBO.getColour())[0])){
				SQL.append("and color in(");
				String[] strs=sDiamondBO.getColour();
				for(int j=0;j<strs.length;j++){
					if(j<strs.length-1)SQL.append("'"+strs[j]+"',");
					else SQL.append("'"+strs[j]+"'");
					URL.append("&sDiamondBo.colour="+strs[j]);
				}
				SQL.append(") ");
			}
			if(sDiamondBO.getPurity()!=null&&sDiamondBO.getPurity().length>0 && !"".equals((sDiamondBO.getPurity())[0])){
				SQL.append("and clarity in(");
				String[] strs=sDiamondBO.getPurity();
				for(int j=0;j<strs.length;j++){
					if(j<strs.length-1)SQL.append("'"+strs[j]+"',");
					else SQL.append("'"+strs[j]+"'");
					URL.append("&sDiamondBo.purity="+strs[j]);
				}
				SQL.append(") ");
			}
			if(sDiamondBO.getCutter()!=null&&sDiamondBO.getCutter().length>0 && !"".equals((sDiamondBO.getCutter())[0])){
				SQL.append("and final_cut in(");
				String[] strs=sDiamondBO.getCutter();
				for(int j=0;j<strs.length;j++){
					if(j<strs.length-1)SQL.append("'"+strs[j]+"',");
					else SQL.append("'"+strs[j]+"'");
					URL.append("&sDiamondBo.cutter="+strs[j]);
				}
				SQL.append(") ");
			}
			if(sDiamondBO.getSymmetry()!=null&&sDiamondBO.getSymmetry().length>0 && !"".equals((sDiamondBO.getSymmetry())[0])){
				SQL.append("and symmetry in(");
				String[] strs=sDiamondBO.getSymmetry();
				for(int j=0;j<strs.length;j++){
					if(j<strs.length-1)SQL.append("'"+strs[j]+"',");
					else SQL.append("'"+strs[j]+"'");
					URL.append("&sDiamondBo.symmetry="+strs[j]);
				}
				SQL.append(") ");
			}
			if(sDiamondBO.getBuffing()!=null&&sDiamondBO.getBuffing().length>0 && !"".equals((sDiamondBO.getBuffing())[0])){
				SQL.append("and polish in(");
				String[] strs=sDiamondBO.getBuffing();
				for(int j=0;j<strs.length;j++){
					if(j<strs.length-1)SQL.append("'"+strs[j]+"',");
					else SQL.append("'"+strs[j]+"'");
					URL.append("&sDiamondBo.buffing="+strs[j]);
				}
				SQL.append(") ");
			}
			if(sDiamondBO.getFluorescence()!=null&&sDiamondBO.getFluorescence().length>0 && !"".equals((sDiamondBO.getFluorescence())[0])){
				SQL.append("and fluor in(" + QueryDiamondAtt.getDiamondAttToFluor(sDiamondBO)+") ");
//				SQL.append("and fluor in(");
//				String[] strs=sDiamondBO.getFluorescence();
//				for(int j=0;j<strs.length;j++){
//					if(j<strs.length-1)SQL.append("'"+strs[j]+"',");
//					else SQL.append("'"+strs[j]+"'");
//					URL.append("&sDiamondBo.fluorescence="+strs[j]);
//				}
//				SQL.append(") ");
			}
			if(sDiamondBO.getCerificate()!=null&&sDiamondBO.getCerificate().length>0 && !"".equals((sDiamondBO.getCerificate())[0])){
				SQL.append("and lab in(");
				String[] strs=sDiamondBO.getCerificate();
				for(int j=0;j<strs.length;j++){
					try {
						strs[j]=URLDecoder.decode(strs[j],"UTF-8");
					} catch (UnsupportedEncodingException e) {
						e.printStackTrace();
					}
					if(j<strs.length-1)SQL.append("'"+strs[j]+"',");
					else SQL.append("'"+strs[j]+"'");
					URL.append("&sDiamondBo.cerificate="+strs[j]);
				}
				sDiamondBO.setCerificate(strs);
				SQL.append(") ");
			}
			String speCondition="";
			if(!StringUtils.isEmpty(sDiamondBO.getMinPrice())&&!StringUtils.isEmpty(sDiamondBO.getMaxPrice())){
				URL.append("&sDiamondBo.minPrice="+sDiamondBO.getMinPrice());
				URL.append("&sDiamondBo.maxPrice="+sDiamondBO.getMaxPrice());
				String priceDif=null;
				if(1==type)priceDif=salePriceSQL;
				if(2==type)priceDif=spePriceSQL;
				if(3==type)priceDif=angentPriceSQL;
				if(4==type)priceDif=suPriceSQL;
				if(priceDif!=null){
					SQL.append("and "+priceDif+">="+sDiamondBO.getMinPrice()+" ");
					SQL.append("and "+priceDif+"<="+sDiamondBO.getMaxPrice()+" ");
					if(1==type){
						SQL.append("or ("+spePriceSQL+">="+sDiamondBO.getMinPrice()+" ");
						SQL.append("and "+spePriceSQL+"<="+sDiamondBO.getMaxPrice()+") ");
					}
				}
			}
		}
		if(2==type){
			SQL.append("and "+spePriceSQL+">0 ");
		}
//		System.out.println(SQL.toString());
//		System.out.println(URL.toString());
		return new String[]{SQL.toString(),URL.toString()};
	}
	
	private String builPriceSQL(List<Formula> formulas){
		if(formulas==null||formulas.size()==0)return "0";
		String end="";
		StringBuffer sql=new StringBuffer();
		for(Formula formula : formulas){
			sql.append(builFormulaSQL(formula));
			end+=")";
		}
		sql.append("0"+end);
		return sql.toString();
	}
	private String builFormulaSQL(Formula formula){
		StringBuffer sql=new StringBuffer("");
		if(formula==null)return sql.toString();
		sql.append("if(1");
		if(!StringUtils.isEmpty(formula.getWeight())){
			String[] weights= formula.getWeight().split("-");
			sql.append("&&weight>="+weights[0]+"&&weight<="+weights[1]);
		}
		if(!StringUtils.isEmpty(formula.getColour())){
			sql.append("&&locate(color,\'"+formula.getColour()+"\')");
		}
		if(!StringUtils.isEmpty(formula.getPurity())){
			sql.append("&&locate(clarity,\'"+formula.getPurity()+"\')");
		}
		if(!StringUtils.isEmpty(formula.getCutter())){
			sql.append("&&locate(final_cut,\'"+formula.getCutter()+"\')");
		}
		if(!StringUtils.isEmpty(formula.getSymmetry())){
			sql.append("&&locate(symmetry,\'"+formula.getSymmetry()+"\')");
		}
		if(!StringUtils.isEmpty(formula.getBuffing())){
			sql.append("&&locate(polish,\'"+formula.getBuffing()+"\')");
		}
		if(!StringUtils.isEmpty(formula.getFluorescence())){
			sql.append("&&locate(fluor,\'"+formula.getFluorescence()+"\')");
		}
		if(!StringUtils.isEmpty(formula.getCerificate())){
			sql.append("&&locate(lab,\'"+formula.getCerificate()+"\')");
		}
		sql.append(","+formula.getRate()+",");
		return sql.toString();
	}

}
